﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Collections;
using TS_XY_Train_Repaire.UserCon.Locomotive;
using System.Data.OleDb;

namespace TS_XY_Train_Repaire.Locomotive.Plane
{
    public partial class Locomotive_Plane_YearPlane : Form
    {
        public Locomotive_Plane_YearPlane()
        {
            InitializeComponent();
        }
        static string fid = string.Empty;
        //绑定列表
        private void _LoadTreeList()
        {
            treeList.Nodes.Clear();
            DataTable _dt = new DataTable();
            treeList.Nodes.Clear();
            treeList.Refresh();
            LeverString_SysTreeMainList _ls = new LeverString_SysTreeMainList();
            ArrayList pList = new ArrayList();
            _ls = new LeverString_SysTreeMainList();
            _ls.f_id = "M-1";
            _ls.f_name = "检修计划列表";
            _ls.f_imgindex = 4;
            pList.Add(_ls);
            try
            {
                string strSQL = "select * from TS_TR_PS_Loco_Repair_Plane_Main where f_deep=0 and f_del=0";
                SqlConnection conn = new SqlConnection(TS_Win_Class.GetSQlConnStr());
                conn.Open();
                SqlCommand cmd = new SqlCommand(strSQL, conn);
                SqlDataReader read = cmd.ExecuteReader();
                //string _strUPID = "and (";
                int i = 0;
                while (read.Read())
                {
                    _ls = new LeverString_SysTreeMainList();
                    _ls.f_id = "T" + read["f_id"].ToString();
                    _ls.f_name = read["f_name"].ToString();
                    _ls.f_parent = "M-1";
                    _ls.f_deep = read["f_deep"].ToString();
                    _ls.f_level = read["f_level"].ToString();
                    _ls.f_stime = read["f_stime"].ToString();
                    _ls.f_etime = read["f_etime"].ToString();
                    _ls.f_type = read["f_type"].ToString();
                    _ls.f_state = read["f_state"].ToString();
                    pList.Add(_ls);
                }

            }
            catch
            {
            }
            treeList.DataSource = pList;
        }
        //加载事件
        private void Locomotive_Plane_YearPlane_Load(object sender, EventArgs e)
        {
            _LoadTreeList();
        }
        //新增计划
        private void barButtonItem3_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {

            UserCon.Locomotive.Plane uc = new UserCon.Locomotive.Plane();
            uc.name = "add";
            uc.ShowDialog();
            if (uc.DialogResult == DialogResult.OK)
            {
                _LoadTreeList();
            }
        }
        //列表点击事件
        private void treeList_FocusedNodeChanged(object sender, DevExpress.XtraTreeList.FocusedNodeChangedEventArgs e)
        {
            bbtaddche.Enabled = false;
            bbtupdche.Enabled = false;
            bbtdelche.Enabled = false;
            barButtonItem4.Enabled = false;
            try
            {
                if (treeList.FocusedNode["f_id"].ToString().Substring(0, 1) == "T")
                {
                    gridControl.DataSource = TS_Win_Class.GetFilledDataTable("select * from TS_TR_PS_Loco_Repair_Plane_Train where f_del=0 and f_mainid=" + treeList.FocusedNode["f_id"].ToString().Substring(1));
                    bbtaddche.Enabled = true;

                    barButtonItem4.Enabled = true;
                }
            }
            catch
            {


            }


        }
        //新增扣车
        private void bbtaddche_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            uc_PlaneKouche uc = new uc_PlaneKouche();
            uc.f_mainid = treeList.FocusedNode["f_id"].ToString().Substring(1);
            uc.ShowDialog();
            if (uc.DialogResult == DialogResult.OK)
            {
                gridControl.DataSource = TS_Win_Class.GetFilledDataTable("select * from TS_TR_PS_Loco_Repair_Plane_Train where f_del=0 and f_mainid=" + treeList.FocusedNode["f_id"].ToString().Substring(1));
            }
        }
        //修改扣车
        private void bbtupdche_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            uc_PlaneKouche uc = new uc_PlaneKouche();
            uc.f_id = fid;
            uc.ShowDialog();
            if (uc.DialogResult == DialogResult.OK)
            {
                gridControl.DataSource = TS_Win_Class.GetFilledDataTable("select * from TS_TR_PS_Loco_Repair_Plane_Train where f_del=0 and f_mainid=" + treeList.FocusedNode["f_id"].ToString().Substring(1));
            }
        }
        //修改计划
        private void barButtonItem4_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            UserCon.Locomotive.Plane uc = new UserCon.Locomotive.Plane();
            uc.name = "upd";
            uc.f_id = treeList.FocusedNode["f_id"].ToString().Substring(1);
            uc.ShowDialog();
            if (uc.DialogResult == DialogResult.OK)
            {
                _LoadTreeList();
            }
        }
        //删除计划
        private void barButtonItem5_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            //UserCon.Locomotive.Plane uc = new UserCon.Locomotive.Plane();
            //uc.name = "del";
            //uc.ShowDialog();
            //if (uc.DialogResult == DialogResult.OK)
            //{
            //    _LoadTreeList();
            //}
            if (MessageBox.Show("确认删除当前计划吗？", "提示", MessageBoxButtons.OKCancel) == DialogResult.OK)
            {
                string sql = "update TS_TR_PS_Loco_Repair_Plane_Main set f_del=1 where f_id=" + treeList.FocusedNode["f_id"].ToString().Substring(1);
                TS_Win_Class.MySqlCom(sql);
                _LoadTreeList();
            }
        }
        //刷新按钮
        private void bbi_Reload_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            _LoadTreeList();
        }
        //grid点击事件
        private void gridControl_Click(object sender, EventArgs e)
        {
            try
            {
                fid = string.Empty;
                fid = gridView1.GetFocusedRowCellValue("f_id").ToString();
                bbtupdche.Enabled = true;
                bbtdelche.Enabled = true;
            }
            catch (Exception)
            {


            }
        }
        //删除扣车
        private void bbtdelche_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (MessageBox.Show("确认删除当前计划扣车吗？", "提示", MessageBoxButtons.OKCancel) == DialogResult.OK)
            {
                string sql = "update TS_TR_PS_Loco_Repair_Plane_Train set f_del=1 where f_id=" + gridView1.GetFocusedRowCellValue("f_id").ToString();
                TS_Win_Class.MySqlCom(sql);
                gridControl.DataSource = TS_Win_Class.GetFilledDataTable("select * from TS_TR_PS_Loco_Repair_Plane_Train where f_del=0 and f_mainid=" + treeList.FocusedNode["f_id"].ToString().Substring(1));
            }
        }
        //导出按钮
        private void daochu_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Title = "导出Excel";
            saveFileDialog.Filter = "Excel文件(*.xls)|*.xls";
            DialogResult dialogResult = saveFileDialog.ShowDialog(this);
            if (dialogResult == DialogResult.OK)
            {
                DevExpress.XtraPrinting.XlsExportOptions options = new DevExpress.XtraPrinting.XlsExportOptions();
                //gridControl1.ExportToXls(saveFileDialog.FileName, options);  
                gridControl.ExportToExcelOld(saveFileDialog.FileName);
                DevExpress.XtraEditors.XtraMessageBox.Show("保存成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
        //关闭事件
        private void Locomotive_Plane_YearPlane_FormClosing(object sender, FormClosingEventArgs e)
        {
            TS_Win_Locomotive_MainForm.htActionItem.Remove(this.Name);
        }


        /// 导入按钮
        private void barButtonItem8_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            string messg = string.Empty;
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                string sql = "";
                if (openFileDialog1.FileName == "")//HasFile用来检查FileUpload是否有指定文件
                {
                    MessageBox.Show("请您选择Excel文件");
                    return;//当无文件时,返回
                }
                string IsXls = System.IO.Path.GetExtension(openFileDialog1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
                if (IsXls != ".xls")
                {
                    MessageBox.Show("只可以选择97-03版本Excel文件");
                    return;//当选择的不是Excel文件时,返回
                }
                string filename = openFileDialog1.SafeFileName;              //获取Execle文件名  DateTime日期函数
                //string savePath = Server.MapPath(("upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径
                string savePath = openFileDialog1.FileName;
                //FileUpload1.SaveAs(savePath);                        //SaveAs 将上传的文件内容保存在服务器上

                DataSet ds = ExcelSqlConnection(savePath, filename);           //调用自定义方法
                DataRow[] dr = ds.Tables[0].Select();            //定义一个DataRow数组
                int rowsnum = ds.Tables[0].Rows.Count;
                if (rowsnum == 0)
                {
                    MessageBox.Show("Excel表为空表,无数据!");   //当Excel表为空时,对用户进行提示
                }
                else
                {
                    bool iret = true;
                    
                    for (int i = 0; i < dr.Length; i++)
                    {
                        //前面除了你需要在建立一个“upfiles”的文件夹外，其他的都不用管了，你只需要通过下面的方式获取Excel的值，然后再将这些值用你的方式去插入到数据库里面

                        string cx = dr[i]["承修段"].ToString();
                        string cxid = TS_Win_Class.GetSQlFieldValue("select f_id from TS_S_T_P_RailwayStations where f_name='" + cx + "' and f_del=0", "f_id");
                        if (cxid == null)
                        {
                            messg += "承修段" + cx + "未找到!\n";
                        }
                        string ps = dr[i]["配属段"].ToString();
                        //                       if (psid == null)
                        //{
                        //    messg += "配属段" + ps + "未找到!\n";
                        //}
                        string jx = dr[i]["机型"].ToString();
                        string jxid = TS_Win_Class.GetSQlFieldValue("select f_id from TS_S_T_P_TrainType where f_name='" + jx + "' and f_del=0", "f_id");
                        if (jxid == null)
                        {
                            messg += "机型" + jx + "未找到!\n";
                        }
                        string ch = dr[i]["车号"].ToString();
                        if (ch.Length != 4)
                        {
                            if (4 - ch.Length == 3) { ch = "000" + ch; }
                            if (4 - ch.Length == 2) { ch = "00" + ch; }
                            if (4 - ch.Length == 1) { ch = "0" + ch; }
                        }
                        string chid=null;
                        string psid=null;
                        if (jxid != null)
                        {
                             chid = TS_Win_Class.GetSQlFieldValue("select f_id from TS_S_T_P_Train_List where f_name='" + ch + "' and f_del=0 and f_ttid=" + jxid, "f_id");
                             if (chid == null||chid=="")
                             {
                                 messg += "机车号" + ch + "未找到!\n";
                             }
                             else
                             {
                                 psid = TS_Win_Class.GetSQlFieldValue("select f_arsid from TS_S_T_P_Train_List where f_id=" + chid + " and f_del=0", "f_arsid");
                             }
                             //if (psid == null) {
                             //    messg += "机车号" + ch + "未找到!\n";
                             //}
                        }
                        string jhyf = dr[i]["计划月份"].ToString();
                        string xc = dr[i]["中次"].ToString();
                        string rpxc = "";
                        if (xc.Length > 2) { rpxc = xc.Substring(0, 1); xc = xc.Substring(1); }
                        string xcid = TS_Win_Class.GetSQlFieldValue("select f_id from TS_S_T_P_Train_RP where f_name='" + xc + "' and f_del=0", "f_id");
                        if (xcid == null)
                        {
                            messg += "修程" + xc + "未找到!\n";
                        }
                        string strq = dr[i]["上台日期"].ToString();
                        string jcrq = dr[i]["交车日期"].ToString();
                        string ldrq = dr[i]["离段日期"].ToString();
                        string f_brief = dr[i]["备注"].ToString();
                        //Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
                        sql += @"insert into TS_TR_PS_Loco_Repair_Plane_Train (f_mainid,
f_trainid,
f_name,
f_ttid,
f_ttname,
f_rrsid,
f_rrsname,
f_arsid,
f_arsname,
f_rpid,
f_rpname,
f_monthname,
f_stime,
f_etime,
f_ltime,
f_brief,
f_state,
f_workerid,
f_rpxc
) values(" + treeList.FocusedNode["f_id"].ToString().Substring(1) + "," + chid + ",'" + ch + "'," + jxid + ",'" + jx + "'," + cxid + ",'" + cx + "'," + psid + ",'" + ps + "'," + xcid + ",'" + xc + "','" + jhyf + "','" + strq + "','" + jcrq + "','" + ldrq + "','" + f_brief + "',0," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'"+rpxc+"')";
                        if (messg != string.Empty) { iret = false; }
                    }
                    if (iret)
                    {
                        TS_Win_Class.MySqlCom(sql);
                        MessageBox.Show("Excle表导入成功!");
                    }
                    else {

                        MessageBox.Show(messg);
                    }
                    //Response.Write("<script>alert('Excle表导入成功!');</script>");
                    
                }
            }
        }
        #region 连接Excel  读取Excel数据   并返回DataSet数据集合
        /// <summary>
        /// 连接Excel  读取Excel数据   并返回DataSet数据集合
        /// </summary>
        /// <param name="filepath">Excel服务器路径</param>
        /// <param name="tableName">Excel表名称</param>
        /// <returns></returns>
        public static System.Data.DataSet ExcelSqlConnection(string filepath, string tableName)
        {
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filepath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
            OleDbConnection ExcelConn = new OleDbConnection(strCon);
            try
            {
                string strCom = string.Format("SELECT * FROM [Sheet1$]");
                ExcelConn.Open();
                OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, ExcelConn);
                DataSet ds = new DataSet();
                myCommand.Fill(ds, "[" + tableName + "$]");
                ExcelConn.Close();
                return ds;
            }
            catch
            {
                ExcelConn.Close();
                return null;
            }
        }
        #endregion
    }
}